<?php
/**
 * Created by PhpStorm.
 * User: alei
 * Date: 11/5/2017 AD
 * Time: 17:09
 */
namespace App\Models\Mysql\tonggao;

//use Illuminate\Database\Eloquent\Model;
use App\Models\Traits\BaseDBMethod;
use Illuminate\Support\Facades\DB;

class term
{
	use BaseDBMethod;
	//数据库连接配置项，见config/database.php
	const DATABASE_CONF = 'tonggao_mysql';
	//pre_ct_ui 首页轮播图所在表
	const TABLE_NAME = 'term';

	//数据库连接配置项，见config/database.php
	//如果需要用ORM，需要设置此项
//	protected $connection = self::DATABASE_CONF;
	//表名，如果需要用ORM，需要设置此项
//	protected $table = self::TABLE_NAME;

	public static function select($sql)
	{
/*		$selectClause = (empty($selectParam['select'])) ? '*' : $selectParam['select'];
		$fromClause = (empty($selectParam['from'])) ? self::TABLE_NAME : $selectParam['from'];
		$sql = "select ".$selectClause." from ".$fromClause;
		foreach (array('where')
		$whereClause = (empty($selectParam['where'])) ? '' : 'where '.$selectParam['where'];
		$groupbyClause = (empty($selectParam['groupby'])) ? '' : 'group by '.$selectParam['group by'];
		$orderbyClause = (empty($selectParam['orderby'])) ? '' : 'order by '.$selectParam['order by'];
		$limitClause = (empty($selectParam['limit'])) ? '' : 'limit '.$selectParam['limit'];*/
		return self::getConnObj()->select($sql);
	}



	#取出术语信息和修改记录
	public  static function term_info($p)
	{
		// $sql="select a.chinese,a.english ,b.modifier,b.modify_time,b.reason from term a left join term_detail b on a.id=b.term_id where a.id=$p['term_id']";
		//       echo $sql;
		return self::getConnObj()->select(
			"SELECT a.id,a.chinese_key,a.chinese_explanation ,a.english,b.sentence_id,b.id as term_detail_id, b.occur_no,b.chinese_pos,b.english_pos,b.tonggao_changed,c.modifier,c.modify_time,c.comment 
             from term a left join term_detail b on a.id=b.term_id  left join term_detail_log c on b.id=c.term_detail_id 
             where b.term_id=:term_id order by c.modify_time desc",
			[
				'term_id'=>$p['term_id']
	        ]
	    );
	}


	public static function getDataByPage($size)
	{
		return self::getDBobj(TRUE)
			->select(DB::raw('count(term.id) mycount, term.id, term.chinese_key as name'))
			->join('term_detail', 'term.id', '=', 'term_detail.term_id')
			->groupBy('term.id')
			->orderBy('mycount', 'desc')
			->orderBy('term.id', 'desc')
			->paginate($size)
			->toArray();
	}

	#更新术语和句子
    public static function update_term($p)
    {
        /*
         * 将term_detail表中当前的记录复制到term_detail_log表中
           计算新的english_pos和diff，并更新term_detail表
           将sentence表中当前的记录复制到sentence_log表中
           更新sentence表
         */
        #执行事务
//        $p = ['term_detail_id' => $this->request->term_detail_id];
//        $p = ['new_sentence_translation' => $this->request->new_sentence_translation];
//        $p = ['new_term_english' => $this->request->new_term_english];
//        $p = ['comment' => $this->request->comment];

        #开启事务
        $t=time();
        self::beginTransaction();
        $term_info=self::getConnObj()->select(
            "SELECT id,english,term_id,last_modifier,sentence_id,last_modify_time,comment from term_detail WHERE id=:id",
            [
                'id'=>$p['term_detail_id']
            ]);

        $r = self::getConnObj()->insert("insert into term_detail_log(`term_detail_id`,`modifier`,`modify_time`,`english`,`comment`)
                              VALUES ({$term_info[0]->id},'{$term_info[0]->last_modifier}','{$term_info[0]->last_modify_time}','{$term_info[0]->english}','{$term_info[0]->comment}')");

        #获取diff方法 更术语表
        //$r1 = self::getConnObj()->update("UPDATE term SET english ='{$p['new_term_english']}'  WHERE  id ={$term_info[0]->term_id}");

        $r2 = self::getConnObj()->update("UPDATE term_detail SET english ='{$p['new_term_english']}' ,comment='{$p['comment']}' , last_modify_time=$t, english_pos='{$p['new_english_pos']}' WHERE  id ={$p['term_detail_id']}");

        $sentence_info=self::getConnObj()->select(
            "SELECT id,translation,last_modifier,last_modify_time,comment from sentence WHERE id=:id",
            [
                'id'=>$term_info[0]->sentence_id
            ]);

        $r3 = self::getConnObj()->insert("insert into sentence_log(`sentence_id`,`modifier`,`modify_time`,`translation`,`comment`)
                              VALUES ({$sentence_info[0]->id},'{$sentence_info[0]->last_modifier}','{$sentence_info[0]->last_modify_time}','{$sentence_info[0]->translation}','{$sentence_info[0]->comment}')");

        #获取diff方法 更新句对表
        $r4 = self::getConnObj()->update("UPDATE sentence SET translation ='{$p['new_sentence_translation']}' ,comment='{$p['comment']}' ,last_modify_time=$t  WHERE  id ={$sentence_info[0]->id}");
		file_put_contents("\\lishu.txt",print_r($r,true).print_r($r2,true).print_r($r3,true).print_r($r4,true),8);
        if($r and $r2 and $r3 and $r4)
        {
            self::commit();
            return true;
        }
        else
        {
            self::rollBack();
			/*foreach(array($r,$r2,$r3,$r4) as $k=>$v){
				if(!$v) echo "$k is empty";
			}*/
            return false;
        }


    }

	
}